package com.youhp.spire.xls.myDemo.Formatting;

import java.awt.Color;

import com.spire.xls.ComparisonOperatorType;
import com.spire.xls.ConditionalFormatType;
import com.spire.xls.ExcelVersion;
import com.spire.xls.IconSetType;
import com.spire.xls.LineStyleType;
import com.spire.xls.Workbook;
import com.spire.xls.Worksheet;
import com.spire.xls.core.IConditionalFormat;
import com.spire.xls.core.spreadsheet.collections.XlsConditionalFormats;
/**
 * 
* @ClassName: ConditionalFormatting 
* @Description: spirexls 条件格式篇
* @author youhp
* @date 2020年7月14日 下午5:28:33
 */
public class ConditionalFormatting {

    public static void main(String[] args) {

        Workbook workbook = new Workbook();
        //加载相对路径下的文件
        workbook.loadFromFile("data/ConditionalFormatting.xlsx");

        //获取第一个工作表
        Worksheet sheet = workbook.getWorksheets().get(0);
        //设置行高和列宽
        sheet.getAllocatedRange().setRowHeight(15);
        sheet.getAllocatedRange().setColumnWidth(16);

        //创建条件格式  大于
        XlsConditionalFormats xcfs1 = sheet.getConditionalFormats().add();
        //条件格式xcfs1适用区域
        xcfs1.addRange(sheet.getCellRange("A1:D1"));
        //条件格式设置
        IConditionalFormat cf1 = xcfs1.addCondition();
        //格式条件为单元格内的值
        cf1.setFormatType(ConditionalFormatType.CellValue);
        //比较值为150
        cf1.setFirstFormula("150");
        //大于150 字体为红色  背景色为粉色
        cf1.setOperator(ComparisonOperatorType.Greater);
        cf1.setFontColor(Color.red);
        cf1.setBackColor(Color.pink);
        
        //创建条件格式 小于
        XlsConditionalFormats xcfs2 = sheet.getConditionalFormats().add();
        xcfs2.addRange(sheet.getCellRange("A2:D2"));
        IConditionalFormat cf2 = xcfs2.addCondition();
        cf2.setFormatType(ConditionalFormatType.CellValue);
        cf2.setFirstFormula("300");
        cf2.setOperator(ComparisonOperatorType.Less);
        //与300对比 小于300 单元格左右边框为粉色左边中等线 右边加粗  上下边框为蓝色双边线
        cf2.setLeftBorderColor(Color.pink);
        cf2.setRightBorderColor(Color.pink);
        cf2.setTopBorderColor(Color.blue);
        cf2.setBottomBorderColor(Color.blue);
        cf2.setLeftBorderStyle(LineStyleType.Medium);
        cf2.setRightBorderStyle(LineStyleType.Thick);
        cf2.setTopBorderStyle(LineStyleType.Double);
        cf2.setBottomBorderStyle(LineStyleType.Double);

        //创建条件格式  数据条  颜色为黄色
        XlsConditionalFormats xcfs3 = sheet.getConditionalFormats().add();
        xcfs3.addRange(sheet.getCellRange("A3:D3"));
        IConditionalFormat cf3 = xcfs3.addCondition();
        cf3.setFormatType(ConditionalFormatType.DataBar);
        cf3.getDataBar().setBarColor(Color.yellow);

        //创建条件格式  图标设置
        XlsConditionalFormats xcfs4 = sheet.getConditionalFormats().add();
        xcfs4.addRange(sheet.getCellRange("A4:D4"));
        IConditionalFormat cf4 = xcfs4.addCondition();
        cf4.setFormatType(ConditionalFormatType.IconSet);
        cf4.getIconSet().setIconSetType(IconSetType.ThreeTrafficLights1);

        //创建条件格式  色阶
        XlsConditionalFormats xcfs5 = sheet.getConditionalFormats().add();
        xcfs5.addRange(sheet.getCellRange("A5:D5"));
        IConditionalFormat cf5 = xcfs5.addCondition();
        cf5.setFormatType(ConditionalFormatType.ColorScale);

        //创建条件格式  重复值高亮
        XlsConditionalFormats xcfs6 = sheet.getConditionalFormats().add();
        xcfs6.addRange(sheet.getCellRange("A6:D6"));
        IConditionalFormat cf6 = xcfs6.addCondition();
        cf6.setFormatType(ConditionalFormatType.DuplicateValues);
        cf6.setBackColor(Color.orange);

        //Save the result file
        workbook.saveToFile("output/conditionalFormatting.xlsx", ExcelVersion.Version2010);

    }
}
